package com.zkmonitor.dao;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.mysql.jdbc.Connection;
import com.zkmonitor.exception.ShepherException;
import com.zkmonitor.model.Cluster;

/**
 * jdbc 操作数据库
 * @author HouYijun
 *
 */
@Repository
public class SnapZkTree {
	@Value("${spring.datasource.url}")
	private String DB_URL; 

	// MySQL的JDBC URL编写方式：jdbc:mysql://主机名称：连接端口/数据库的名称
	@Value("${spring.datasource.username}")
	private String USER;
	@Value("${spring.datasource.password}")
	private String PASS;
	
	static final String TABLE = "zk_snap";

	Connection conn = null;

	public boolean isConnected() {
		try {
			if (conn != null && !conn.isClosed()) {
				return true;
			}
		} catch (Exception e) {

		}
		return false;
	}

	@PostConstruct
	public synchronized void connect() {
		if (conn == null) {
			try {
				// 注册驱动
				Class.forName("com.mysql.jdbc.Driver");

				// 创建链接
				conn = (Connection) DriverManager.getConnection(DB_URL, USER,PASS);
				System.out.println("connect mysql closed?:"+conn.isClosed());
			} catch (Exception e) {

			}
		}
	}

	@PreDestroy
	public synchronized void close() {
		if (conn != null) {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}

	public List<String> query_clustor(String clustor) throws SQLException,
			Exception {
		if (!this.isConnected()) {
			connect();
		}
		List<String> resp = new ArrayList<String>();

		Statement stat = null;
		stat = conn.createStatement();
		String sql = "SELECT * FROM " + TABLE + " where clustor='" + clustor
				+ "' order by node";
		ResultSet rs = stat.executeQuery(sql);
		// 输出查询结果
		while (rs.next()) {
			resp.add(rs.getString("node"));
		}
		// 关闭
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return resp;
	}

	public void clear_clustor(String clustor) throws SQLException, Exception {
		if (!this.isConnected()) {
			connect();
		}

		Statement stat = null;
		try {
			stat = conn.createStatement();
			String sql = "delete from  " + TABLE + " where clustor='" + clustor
					+ "'";
			boolean success = stat.execute(sql);
			if (!success) {
				System.out.println("clear " + TABLE + " error for clustor:"
						+ clustor);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stat != null) {
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void insert_db(String clustor, List<String> nodes)
			throws SQLException, Exception {
		if (!this.isConnected()) {
			connect();
		}
		if (StringUtils.isEmpty(clustor) || nodes == null | nodes.isEmpty())
			return;
		Statement stat = null;
		stat = conn.createStatement();
		for (String node : nodes) {
			// 关闭
			try {
				String sql = "insert into zk_snap(clustor,node) values('${clustor}','${node}')";
				sql = sql.replace("${clustor}", clustor).replace("${node}",
						node);
				boolean success = stat.execute(sql);
				if (!success) {
					System.out.println("insert into zk_snap(" + clustor
							+ ")errr.(" + node + ")");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		// finally {
		try {
			if (stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Cluster Mysql operations
	 */
	public void cluster_create(String name, String config)
			throws ShepherException, SQLException, Exception {
		if (StringUtils.isBlank(config) || StringUtils.isBlank(name)) {
			throw ShepherException.createIllegalParameterException();
		}

		if (!this.isConnected()) {
			connect();
		}

		List<Cluster> clusters = cluster_all();
		for (Cluster c : clusters) {
			if (c.getName().equals(name)) {
				throw ShepherException.createDuplicateKeyException();
			}
		}

		Statement stat = null;
		stat = conn.createStatement();
		try {
			String sql = "INSERT INTO cluster(name, config) VALUES (#{name}, #{config})";
			sql = sql.replace("#{name}", "'" + name + "'");
			sql = sql.replace("#{config}", "'" + config + "'");
			boolean success = stat.execute(sql);
			if (!success) {
				System.out.println("insert into cluster error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// finally {
		try {
			if (stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void cluster_update(String name, String config)
			throws ShepherException, SQLException, Exception {
		if (StringUtils.isBlank(config) || StringUtils.isBlank(name)) {
			throw ShepherException.createIllegalParameterException();
		}

		if (!this.isConnected()) {
			connect();
		}

		Statement stat = null;
		stat = conn.createStatement();
		try {
			String sql = "UPDATE cluster SET config = #{config} WHERE name = #{name}";
			sql = sql.replace("#{name}", "'" + name + "'");
			sql = sql.replace("#{config}", "'" + config + "'");
			boolean success = stat.execute(sql);
			if (!success) {
				System.out.println("insert into cluster error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// finally {
		try {
			if (stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void cluster_delete(String name)  {
		if (StringUtils.isBlank(name)) {
			return;
		}

		if (!this.isConnected()) {
			connect();
		}

		
		try {
			Statement stat = null;
			stat = conn.createStatement();
			String sql = "delete from cluster  where name='" + name + "'";
			boolean success = stat.execute(sql);
			if (!success) {
				System.out.println("delete from cluster error");
			}
			if (stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}

	public List<Cluster> cluster_all()  {
		if (!this.isConnected()) {
			connect();
		}
		List<Cluster> resp = new ArrayList<Cluster>();

		
		// 关闭
		try {
			Statement stat = null;
			stat = conn.createStatement();
			String sql = "SELECT * FROM cluster order by time";
			ResultSet rs = stat.executeQuery(sql);
			// 输出查询结果
			while (rs.next()) {
				Cluster c = new Cluster();
				c.setName(rs.getString("name"));
				c.setConfig(rs.getString("config"));
				c.setId(rs.getLong("id"));
				resp.add(c);
			}
			if (rs != null) {
				rs.close();
			}
			if (stat != null) {
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
		}
		return resp;
	}

}
